﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;

namespace Pentakill
{
    public partial class Employee : Form
    {
        SqlConnection conn = new SqlConnection();
        SqlDataAdapter da;
        DataSet ds;
        SqlCommandBuilder cb;
        DataTable dt;
        DataView dv;

        public Employee()
        {
            InitializeComponent();
            string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            conn.ConnectionString = constr;
            InitializeADOObject();

            LoadToC();
            LoadTitle();
            LoadmgrId();
            LoadFiterTitle();
        }

        bool validateInput()
        {
            string name = txtLastName.Text;
            string name1 = txtFirstName.Text;
            bool error = false;
            if (name.Length == 0)
            {
                errorProvider1.SetError(txtLastName, "Please enter your Last name");
                error = true;
            }

            if (name1.Length == 0)
            {
                errorProvider1.SetError(txtFirstName, "Please enter your First name");
                error = true;
            }
            string title = cmbTitle.Text;
            if (title.Length == 0)
            {
                errorProvider1.SetError(cmbTitle, "Please enter your title");
                error = true;
            }
            if (mtxtPhone.MaskCompleted == false)
            {
                errorProvider1.SetError(mtxtPhone, "Please enter your Phonenumber");
                error = true;
            }
            string post = mtxtPostalcode.Text.Trim();
            if (post.Length > 0 && mtxtPostalcode.MaskCompleted == false)
            {
                errorProvider1.SetError(mtxtPostalcode, "Please enter your Postalcode");
                error = true;
            }
            string toc = cmbToC.Text;
            if (toc.Length == 0)
            {
                errorProvider1.SetError(cmbToC, "Please enter your Title of Courtesy");
                error = true;
            }
            string country =cmbCountry.Text;
            if (country.Length == 0)
            {
                errorProvider1.SetError(cmbCountry, "Please enter your Country");
                error = true;
            }
            DateTime currDate = DateTime.Now;
            int currYear = currDate.Year;
            DateTime DateofBirth = dtbBirthday.Value;
            int birthYear = DateofBirth.Year;
            if (currYear - birthYear < 18)
            {
                errorProvider1.SetError(dtbBirthday, " Age must be greater than or equal 18");
                error = true;
            }

            
            DateTime DateofHire = dtbHiredate.Value;
            int HireYear = DateofHire.Year;
            if (HireYear - birthYear < 18)
            {
                errorProvider1.SetError(dtbHiredate, " HireYear must be greater than or equal birthYear 18 years");
                error = true;
            }

            string add = txtAddress.Text; ;
            if (add.Length == 0)
            {
                errorProvider1.SetError(txtAddress, "Please enter your Address");
                error = true;
            }
            string city = txtCity.Text;
            if (city.Length == 0)
            {
                errorProvider1.SetError(txtCity, "Please enter your City");
                error = true;
            }
            if (error == true)
            {
                return false;
            }
            else
                errorProvider1.Clear();
            return true;
        }

        #region //load title, ToC, mgrId,filterTitle
        void LoadTitle()
        {
           
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;

            SqlDataAdapter da = new SqlDataAdapter("SELECT DISTINCT title FROM HR.Employees", conn);
            DataTable dt = new DataTable("HR.Employees");
            da.Fill(dt);
            cmbTitle.DataSource = dt;
            cmbTitle.DisplayMember = "title";//cot hien ra trong giao dien
            cmbTitle.ValueMember = "title";
      

        }
        void LoadToC()
        {

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;

            SqlDataAdapter da = new SqlDataAdapter("SELECT DISTINCT titleofcourtesy FROM HR.Employees", conn);
            DataTable dt = new DataTable("HR.Employees");
            da.Fill(dt);
            cmbToC.DataSource = dt;
            cmbToC.DisplayMember = "titleofcourtesy";//cot hien ra trong giao dien
            cmbToC.ValueMember = "titleofcourtesy";

        }
        void LoadmgrId()
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;

            SqlDataAdapter da = new SqlDataAdapter("SELECT CAST(empid AS nvarchar)+' '+firstname+' '+lastname AS fullname, empid FROM HR.Employees", conn);

            DataTable dt = new DataTable("HR.Employees");
            da.Fill(dt);
            cmbMgrid.DataSource = dt;
            cmbMgrid.DisplayMember = "fullname";//cot hien ra trong giao dien
            cmbMgrid.ValueMember = "empid";
       
        }
        void LoadFiterTitle()
        {

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;

            SqlDataAdapter da = new SqlDataAdapter("SELECT DISTINCT title FROM HR.Employees", conn);
            DataTable dt = new DataTable("HR.Employees");
            da.Fill(dt);
            cmbFilterTitle.DataSource = dt;
            cmbFilterTitle.DisplayMember = "title";//cot hien ra trong giao dien
            cmbFilterTitle.ValueMember = "title";
 

        }

        #endregion

        #region // loadEmployee

        void InitializeADOObject()
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "getEmployee";
            da = new SqlDataAdapter(cmd);
            cb = new SqlCommandBuilder(da);
            ds = new DataSet();
            da.FillSchema(ds, SchemaType.Mapped);
            da.Fill(ds, "HR.Employees");

        }
        void LoadEmployeeInfo()
        {
            dt = ds.Tables["HR.Employees"];
            dv = new DataView(dt);
            gvEmployee.DataSource = dt;
        }

        private void Employee_Load(object sender, EventArgs e)
        {
            LoadEmployeeInfo();
        }
        #endregion

        #region //add, update, delete, new
        void addEmployee()
        {
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "LoadEmployeeTable";

                SqlParameter param = new SqlParameter("@lastname", SqlDbType.NVarChar, 20);
                param.Value = txtLastName.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@firstname", SqlDbType.NVarChar, 10);
                param.Value = txtFirstName.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@title", SqlDbType.NVarChar, 30);
                param.Value = cmbTitle.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@titleofcourtesy", SqlDbType.NVarChar, 25);
                param.Value = cmbToC.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@birthdate", SqlDbType.DateTime);
                param.Value = dtbBirthday.Value.ToShortDateString();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@hiredate", SqlDbType.DateTime);
                param.Value = dtbHiredate.Value.ToShortDateString();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@address", SqlDbType.NVarChar, 60);
                param.Value = txtAddress.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@city", SqlDbType.NVarChar, 15);
                param.Value = txtCity.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@region", SqlDbType.NVarChar, 15);
                if (txtRegion.Text.Length == 0)
                    param.Value = DBNull.Value;
                else
                    param.Value = txtRegion.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@postalcode", SqlDbType.NVarChar, 10);
                if (mtxtPostalcode.Text.Length == 0)
                    param.Value = DBNull.Value;
                else
                    param.Value = mtxtPostalcode.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@country", SqlDbType.NVarChar, 15);
                param.Value = cmbCountry.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@phone", SqlDbType.NVarChar, 24);
                param.Value = mtxtPhone.Text;
                cmd.Parameters.Add(param);

                if (cbMgrid.Checked == true)
                {
                    param = new SqlParameter("@mgrid", SqlDbType.Int);
                    param.Value = int.Parse(cmbMgrid.SelectedValue.ToString());
                    cmd.Parameters.Add(param);
                }
                else
                {
                    param = new SqlParameter("@mgrid", SqlDbType.Int);
                    param.Value = System.Convert.DBNull;
                    cmd.Parameters.Add(param);
                }

                cmd.ExecuteNonQuery();

                InitializeADOObject();
                LoadEmployeeInfo();
                MessageBox.Show("Add Successfully");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        void updateEmployee()
        {
            try
            {
                if (validateInput())
                {

                    conn.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "UpdateEmployeeTable";

                    SqlParameter param = new SqlParameter("@lastname", SqlDbType.NVarChar, 20);
                    param.Value = txtLastName.Text;
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@empId", SqlDbType.Int);
                    param.Value = int.Parse(txtId.Text);
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@firstname", SqlDbType.NVarChar, 10);
                    param.Value = txtFirstName.Text;
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@title", SqlDbType.NVarChar, 30);
                    param.Value = cmbTitle.Text;
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@titleofcourtesy", SqlDbType.NVarChar, 25);
                    param.Value = cmbToC.Text;
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@birthdate", SqlDbType.DateTime);
                    param.Value = dtbBirthday.Value.ToShortDateString();
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@hiredate", SqlDbType.DateTime);
                    param.Value = dtbHiredate.Value.ToShortDateString();
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@address", SqlDbType.NVarChar, 60);
                    param.Value = txtAddress.Text;
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@city", SqlDbType.NVarChar, 15);
                    param.Value = txtLastName.Text;
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@region", SqlDbType.NVarChar, 15);
                    if (txtRegion.Text.Length == 0)
                        param.Value = DBNull.Value;
                    else
                        param.Value = txtRegion.Text;
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@postalcode", SqlDbType.NVarChar, 10);
                    if (mtxtPostalcode.Text.Length == 0)
                        param.Value = DBNull.Value;
                    else
                        param.Value = mtxtPostalcode.Text;
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@country", SqlDbType.NVarChar, 15);
                    param.Value = cmbCountry.Text;
                    cmd.Parameters.Add(param);

                    param = new SqlParameter("@phone", SqlDbType.NVarChar, 24);
                    param.Value = mtxtPhone.Text;
                    cmd.Parameters.Add(param);

                    if (cbMgrid.Checked == true)
                    {
                        param = new SqlParameter("@mgrid", SqlDbType.Int);
                        param.Value = int.Parse(cmbMgrid.SelectedValue.ToString());
                        cmd.Parameters.Add(param);
                    }
                    else
                    {
                        param = new SqlParameter("@mgrid", SqlDbType.Int);
                        param.Value = System.Convert.DBNull;
                        cmd.Parameters.Add(param);
                    }
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("Update Successfull");
                }
            }catch(Exception ex){
                MessageBox.Show(ex.Message);
            }finally{

                conn.Close();
                InitializeADOObject();
                LoadEmployeeInfo();
                LoadToC();
                LoadTitle();
                LoadmgrId();
                LoadFiterTitle();
            }
        }

        void deleteEmployee()
        {

            conn.Open();
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "deleteEmployee";

                SqlParameter param = new SqlParameter("@empId", SqlDbType.Int);
                param.Value = int.Parse(txtId.Text);
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();
                MessageBox.Show("Delete successfully!");
            }
            catch (Exception ex)
            {
                MessageBox.Show("Can not delete Employee");
            }
            finally
            {
                InitializeADOObject();
                LoadEmployeeInfo();
                conn.Close();
            }
        }

        void newEmployee()
        {
            errorProvider1.Clear();
            txtFirstName.Text = "";
            txtLastName.Text = "";
            cmbTitle.Text = "";
            cmbToC.Text = "";
            dtbBirthday.Value = DateTime.Now;
            dtbHiredate.Value = DateTime.Now;
            txtAddress.Text = "";
            txtCity.Text = "";
            txtRegion.Text = "";
            mtxtPostalcode.Text = "";
            cmbCountry.Text = "";
            mtxtPhone.Text = "";
            InitializeADOObject();
            LoadEmployeeInfo();
            LoadToC();
            LoadTitle();
            LoadmgrId();
            LoadFiterTitle();
        }
        #endregion

        #region //search

        void SearchByLastName()
        {
            dv = new DataView(dt);
            dv.RowFilter = string.Format("lastname like '{0}%'", txtSearch.Text);
            gvEmployee.DataSource = dv;
        }

        void SearchByFirstName()
        {
            dv = new DataView(dt);
            dv.RowFilter = string.Format("firstname like '{0}%'", txtSearch.Text);
            gvEmployee.DataSource = dv;
        }
        void SearchByTitle()
        {
            dv = new DataView(dt);
            dv.RowFilter = string.Format("title like '{0}%'", cmbFilterTitle.Text);
            gvEmployee.DataSource = dv;
        }
        void SearchByCountry()
        {
            dv = new DataView(dt);
            dv.RowFilter = string.Format("country like '{0}%'", cmbFilterCountry.Text);
            gvEmployee.DataSource = dv;
        }
        #endregion.

        #region //action

        private void btnAdd_Click(object sender, EventArgs e)
        {
            errorProvider1.Clear();
            if (validateInput() == false)
            {
                return;
            }
            addEmployee();

        }

        private void gvEmployee_SelectionChanged(object sender, EventArgs e)
        {
            if (gvEmployee.SelectedRows.Count > 0)
            {
                btnUpdate.Enabled = true;
                btnDelete.Enabled = true;
                btnAdd.Enabled = false;
                DataGridViewRow r = gvEmployee.SelectedRows[0];
                txtId.Text = r.Cells[0].Value.ToString();
                txtLastName.Text = r.Cells[1].Value.ToString();
                txtFirstName.Text = r.Cells[2].Value.ToString();
                cmbTitle.Text = r.Cells[3].Value.ToString();
                cmbToC.Text = r.Cells[4].Value.ToString();

                DateTime bd;
                DateTime.TryParse(r.Cells[5].Value.ToString(), out bd);
                dtbBirthday.Value = bd;

                DateTime hd;
                DateTime.TryParse(r.Cells[6].Value.ToString(), out hd);
                dtbHiredate.Value = hd;

                txtAddress.Text = r.Cells[7].Value.ToString();
                txtCity.Text = r.Cells[8].Value.ToString();
                txtRegion.Text = r.Cells[9].Value.ToString();
                mtxtPostalcode.Text = r.Cells[10].Value.ToString();
                cmbCountry.Text = r.Cells[11].Value.ToString();
                mtxtPhone.Text = r.Cells[12].Value.ToString();
                cmbMgrid.SelectedValue = r.Cells[13].Value;
            }
        }

        private void btnSearchLastname_Click(object sender, EventArgs e)
        {
            SearchByLastName();
        }

        private void btnSearchFirstname_Click(object sender, EventArgs e)
        {
            SearchByFirstName();
        }

        private void btnFilterTitle_Click(object sender, EventArgs e)
        {
            SearchByTitle();
        }

        private void btnFilterCountry_Click(object sender, EventArgs e)
        {
            SearchByCountry();
        }

        private void btnNew_Click(object sender, EventArgs e)
        {
            newEmployee();
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            updateEmployee();
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            try
            {
                deleteEmployee();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Can not delete Employee");
            }
        }

        private void cbMgrid_CheckedChanged(object sender, EventArgs e)
        {
            if (cbMgrid.Checked == true)
            {
                cmbMgrid.Enabled = true;
            }
            else
            {
                cmbMgrid.Enabled = false;
            }
        }

        private void Employee_FormClosing(object sender, FormClosingEventArgs e)
        {
            DialogResult result;
            result = MessageBox.Show("Do you want to Exit?", "Exit", MessageBoxButtons.OKCancel);
            if (result == DialogResult.Cancel)
            {
                e.Cancel = true;
            }
            this.Hide();
            this.Parent = null;
            e.Cancel = true;
        }
        #endregion

        private void txtLastName_KeyDown(object sender, KeyEventArgs e)
        {
            if ((e.KeyCode >= Keys.D0 && e.KeyCode <= Keys.D9) || (e.KeyCode >= Keys.NumPad0 && e.KeyCode <= Keys.NumPad9))
            {
                e.SuppressKeyPress = true;
            }
            else
            {
                e.SuppressKeyPress = false;
            }
        }

        private void txtFirstName_KeyDown(object sender, KeyEventArgs e)
        {
            if ((e.KeyCode >= Keys.D0 && e.KeyCode <= Keys.D9) || (e.KeyCode >= Keys.NumPad0 && e.KeyCode <= Keys.NumPad9))
            {
                e.SuppressKeyPress = true;
            }
            else
            {
                e.SuppressKeyPress = false;
            }
        }

       

    }
}

